package com.webapp.model;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
/*
 * Class used to connect to the database and gather each application, store it in an ArrayList of 
 * applications and return this list to be displayed.
 */
public class ViewTableModel{
	
	private static Connection conn = null;
	private static String driver = "com.mysql.jdbc.Driver";
	private static String dbUrl = "jdbc:mysql://mysql1.cs.stonybrook.edu:3306/gdonnelly";
	private static String dbPassword = "106338300";
	private static String dbUser = "gdonnelly";
	
	//list of applications to be used for a table of applications
	private static ArrayList<Application> appList;
	
	
	/*
	 * Data for View Table: 
	 * Name (first, middle, last), email, 
	 * research area of interest, UG institution, UG GPA, Grad institution, Grad GPA
	 * OTS scores, tier, summary, average rating, reviews, link to PDF file
	 * 
	 * USES APP ID AS A REFERENCE
	 */
	public static ArrayList<Application> getTable(){
		
		appList = new ArrayList<Application>(100);
		
		try{
			Statement stmt = connect();
			ResultSet rs;
			
			//gets Name, AppId and email from database, creates Application obj and adds to appList
			rs = stmt.executeQuery("select * from ay_applicant_general_info");
			while(rs.next()){
				Application app = new Application();
				app.setAppId(rs.getInt(2));
				app.setFirstName(rs.getString(3));
				app.setMiddleName(rs.getString(5));
				app.setLastName(rs.getString(4));
				app.setEmail(rs.getString(1));
				
				appList.add(app);
			}
			
			//gets research area of interest
			rs = stmt.executeQuery("select * from ay_applicant_edu_background");
			while(rs.next()){
				String email = rs.getString(1);
				String area = rs.getString(3);
				//goes through all current applications
				for(int i = 0; i < appList.size(); i++){
					//determines which is the correct application, based on email
					if(appList.get(i).getEmail().equals(email)){
						//sets research area of interest
						appList.get(i).setResearchArea(area);
						break;
					}
				}
			}
			
			//undergrad institution AND GPA
			rs = stmt.executeQuery("select * from ay_applicant_ug_insititute");
			while(rs.next()){
				String email = rs.getString(1);
				String inst = rs.getString(3);
				double gpa = rs.getDouble(4);

				for(int i = 0; i < appList.size(); i++){
					if(appList.get(i).getEmail().equals(email)){
						appList.get(i).setUgInst(inst);
						appList.get(i).setUgGpa(gpa);
						break;
					}
				}
			}
			
			//grad institution AND GPA
			rs = stmt.executeQuery("select * from ay_applicant_grad_insititution");
			while(rs.next()){
				String email = rs.getString(1);
				String inst = rs.getString(3);
				double gpa = rs.getDouble(4);
				
				for(int i = 0; i < appList.size(); i++){
					if(appList.get(i).getEmail().equals(email)){
						appList.get(i).setGradInst(inst);
						appList.get(i).setGradGpa(gpa);
						break;
					}
				}
			}
			
			//OTS gre scores (v, q, a, subj)
			rs = stmt.executeQuery("select * from ots_applicant_gre_score");
			while(rs.next()){
				String email = rs.getString(1);
				int greV = rs.getInt(3);
				int greQ = rs.getInt(5);
				int greA = rs.getInt(7);
				int greSubj = rs.getInt(9);
				
				for(int i = 0; i < appList.size(); i++){
					if(appList.get(i).getEmail().equals(email)){
						appList.get(i).setOfclGreV(greV);
						appList.get(i).setOfclGreQ(greQ);
						appList.get(i).setOfcileGreA(greA);
						appList.get(i).setOfclGreSubj(greSubj);
						break;
					}
				}
			}
			
			//OTS toefl scores (total, list, read, write, speak)
			rs = stmt.executeQuery("select * from ots_applicant_toefl_scores");
			while(rs.next()){
				String email = rs.getString(1);
				int total = rs.getInt(3);
				int listen = rs.getInt(4);
				int read = rs.getInt(5);
				int speak = rs.getInt(6);
				int write = rs.getInt(7);
				
				for(int i = 0; i < appList.size(); i++){
					if(appList.get(i).getEmail().equals(email)){
						appList.get(i).setOfclToeflTotal(total);
						appList.get(i).setOfclToeflListen(listen);
						appList.get(i).setOfclToeflRead(read);
						appList.get(i).setOfclToeflSpeak(speak);
						appList.get(i).setOfclToeflWrite(write);
						break;
					}
				}
			}
			
			//tier, summary, average rating, number reviews
			rs = stmt.executeQuery("select * from app_general_info");
			while(rs.next()){
				String email = rs.getString(1);
				float tier = rs.getFloat(7);
				String summary = rs.getString(8);
				float avgRating = rs.getFloat(11);
				int numReviews = rs.getInt(10);
				
				for(int i = 0; i < appList.size(); i ++){
					if(appList.get(i).getEmail().equals(email)){
						appList.get(i).setTier(tier);
						appList.get(i).setSummary(summary);
						appList.get(i).setAvgRating(avgRating);
						appList.get(i).setNumDesiredReviews(numReviews);
					}
				}
			}
			
			//ratings and reviews
			rs = stmt.executeQuery("select * from app_reviews");
			while(rs.next()){
				String email = rs.getString(1);
				int reviewNum = rs.getInt(3);
				double rating = rs.getDouble(4);
				String review = rs.getString(5);
				
				for(int i = 0; i < appList.size(); i++){
					if(appList.get(i).getEmail().equals(email)){
						if(reviewNum == 1){
							appList.get(i).setRating1(rating);
							appList.get(i).setReview1(review);
						}
						else if(reviewNum == 2){
							appList.get(i).setRating2(rating);
							appList.get(i).setReview2(review);
						}
						else if(reviewNum == 3){
							appList.get(i).setRating3(rating);
							appList.get(i).setReview3(review);
						}
						else if(reviewNum == 4){
							appList.get(i).setRating4(rating);
							appList.get(i).setReview4(review);
						}
						else if(reviewNum == 5){
							appList.get(i).setRating5(rating);
							appList.get(i).setReview5(review);
						}
						break;
					}
				}
			}
			
			//close the connection
			rs.close();
			stmt.close();
			conn.close();
			
			return appList;
		}
		catch(Exception ex){
			System.out.println("Could not connect to database");
			return null;
		}
	}
	
	public ArrayList<Application> getNeededApps(String email){
		ArrayList<Application> list = new ArrayList<Application>();
		try{
			ResultSet rs;
			Statement stmt = connect();
			
			rs = stmt.executeQuery("SELECT applicant_email FROM user_needs_review WHERE user_email = '" +email+ "'");
			
			//get all email address the USER needs to review
			ArrayList<String> appEmail = new ArrayList<String>();
			while(rs.next())
				appEmail.add(rs.getString(1));
			
			//get the full application for the specific applicant
			for(int i = 0; i < appEmail.size(); i++){
				list.add(ViewApplicationModel.getApplication(appEmail.get(i)));
			}
			
			
			return list;
		}catch(Exception ex){return null;}
	}
	
	public static Statement connect(){
		try{
			Class.forName(driver);
			conn = (Connection) DriverManager.getConnection(dbUrl, dbUser, dbPassword);
			Statement stmt = (Statement) conn.createStatement();
			
			return stmt;
		}catch(Exception ex){return null;}
	}
}